import socket
import urllib.request
import urllib.error

import pymysql
from bs4 import BeautifulSoup

def insert(sn, name):
    # 打开数据库连接
    db = pymysql.connect("localhost", "admin", "Admin123", "test", charset='utf8')

    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # SQL 插入语句
    sql = "INSERT INTO rzdjr_dtl(sn, name) VALUES ('%s', '%s' )"

    try:
        # 执行sql语句
        cursor.execute(sql % (sn, name))
        # 提交到数据库执行
        db.commit()
    except Exception as e:
        print(e)
        db.rollback()
    # 关闭数据库连接
    db.close()


def insertBatch(batch):
    # 打开数据库连接
    db = pymysql.connect("localhost", "admin", "Admin123", "test", charset='utf8')

    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # SQL 插入语句
    sql = "INSERT INTO rzdjr_dtl(sn, name) VALUES ('%s', '%s' )"

    for one in batch:
        try:
            # 执行sql语句
            cursor.execute(sql % (one[0], one[1]))
            # 提交到数据库执行
            db.commit()
        except Exception as e:
            print(e)
            db.rollback()
    # 关闭数据库连接
    db.close()


socket.setdefaulttimeout(20)  # 设置socket层的超时时间为20秒

header = {'User-Agent': 'Mozilla/5.0'}

request = urllib.request.Request('http://www.gzccb.com/bhgg/201909/t20190926_3194.html', headers=header)

try:

    response = urllib.request.urlopen(request)
    soup = BeautifulSoup(response, 'html.parser')
    table = soup.find('table', attrs={'class': 'MsoTableGrid'})
    trs = table.find_all("tr")
    batch = []
    for i in range(0, len(trs)):
        if i == 0:
            # 列名
            continue
        else:
            # 列值
            tds = trs[i].find_all('td')
            td0_txt = str.strip(tds[0].get_text())
            td1_txt = str.strip(tds[1].get_text())
            one = []
            if td0_txt == '30':
                print('this is vip:')
            print('sn=%s name=%s' % (td0_txt, td1_txt))
            one.append(td0_txt)
            one.append(td1_txt)
            batch.append(one)
    insertBatch(batch)
    response.close()  # 注意关闭response

except urllib.error.URLError as e:
    print(e.reason)
